<?php
// +----------------------------------------------------------------------
// | title: 标题注释
// +----------------------------------------------------------------------
// | Author: TZ.Leong
// +----------------------------------------------------------------------
// | Date: 2019/11/19 16:49
// +----------------------------------------------------------------------

namespace app\single\model;


use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\facade\Log;
use think\Model;
use think\Db;

class LogLoginModel extends Model
{
    private $LOGIN = 0;  //登录
    private $LOGINOUT = 1; //登出

    // Example usage in methods:
    // WHERE type = self::LOGIN
    // WHERE type = self::LOGINOUT
    public function initialize()
    {
        $this->table = config("database.log") . ".log_login";
    }

    /**
     * 获取玩家登入登出数量
     * @param int $day 统计日期
     * @return mixed
     */
    public function getLoginInfo(int $day)
    {
        //        print_r($this->getLastSql());
//        print_r($data);
//        ->join([config("database.game").".role" => "r"], "r.id = log.role_id")
//            ->leftJoin([config("database.game").".role_pay_total" => "rpt"], "rpt.role_id = log.role_id")
//            ->field("log.role_id,r.platform,r.accname,r.server_id,r.level,r.power,r.reg_time,rpt.rmb,
//            SUM(CASE WHEN operation = 2 THEN online_time ELSE 0 END) AS online_time,
//            SUM(CASE WHEN operation = 1 THEN 1 ELSE 0 END) AS login_num")
        try {

            $start_time = strtotime($day . ' 00:00:00');
            $end_time = strtotime($day . ' 23:59:59');
            $querysql= "SELECT 
    log.role_id,
    r.pf as platform,
    r.account as accname,
    r.zone_id as server_id,
    r.lev as level,
    r.fc as fight,
    r.reg_time,
    rpt.rmb,
    FROM_UNIXTIME(time, \"%Y-%m-%d\") as day,
    SUM(CASE WHEN `type` = 1 THEN online_time ELSE 0 END) AS online_time,
    SUM(CASE WHEN `type` = 0 THEN 1 ELSE 0 END) AS login_num 
FROM ".config("database.game").".`log_login` `log` 
INNER JOIN ".config("database.game").".`role` `r` ON `r`.`id` = log.role_id 
    AND time BETWEEN $start_time AND $end_time 
LEFT JOIN `role_pay_total` `rpt` ON `rpt`.`role_id` = `log`.`role_id` 
GROUP BY `log`.`role_id`;
            ";
            $data =Db::query($querysql);
            echo "getLoginInfo_sql = >" . $this->getLastSql() . "LINE" . __LINE__ . PHP_EOL;
            return $data;
        } catch (DataNotFoundException | ModelNotFoundException | DbException $e) {
            echo "error:" . $e->getMessage() . "last query sql = >" . $this->getLastSql() . "LINE" . __LINE__ . PHP_EOL;
            return [];
        }
    }


    public function getUserByOfflineUserid($userid, $pf)
    {
        $roleTable = config("database.log") . ".role";
        $querysql = "SELECT COALESCE(MAX(ll.role_offline_time), 0) AS last_offline_time
FROM $roleTable r
INNER JOIN $this->table ll ON r.id = ll.role_id
WHERE r.account = '$userid' AND r.pf = '$pf'";
        $data = Db::query($querysql);
        $lastOfflineTime = $data[0]["last_offline_time"] ?? 0;
        return ($lastOfflineTime > 0 && $lastOfflineTime <= time()) ? 1 : 0;
    }


    /**
     * 获取离线经验达到上限or 灵兽家园收益达到上限的
     */
    public function getUserByOfflineOrSpiritBeast($pf)
    {
        //todo:

        $roleTable = config("database.log") . ".role";
        /**
         * @var mixed
         * 1.玩家离线经验达到上限 2.灵兽家园收益达到上限
         */


        $sql1 = "SELECT r.account
FROM 
    $roleTable  r
INNER JOIN (
    SELECT 
        role_id,
        MAX(time) AS latest_time
    FROM 
        $this->table
    WHERE 
         `type` = $this->LOGINOUT
        AND role_offline_time != 0
    GROUP BY 
        role_id
) AS latest ON r.id = latest.role_id
INNER JOIN 
    $this->table l ON l.role_id = latest.role_id 
               AND l.time = latest.latest_time
               AND l.type = $this->LOGINOUT
WHERE 
    r.is_online = 0
    AND r.pf= '$pf'
    AND l.role_offline_time <= UNIX_TIMESTAMP();";

        // Log::write("debug sql1===<>".$sql1 );

        $sql2 = "SELECT r.account
FROM 
    $roleTable  r
INNER JOIN (
    SELECT 
        role_id,
        MAX(time) AS latest_time
    FROM 
        $this->table
    WHERE 
        `type` = $this->LOGINOUT
        AND animal_home_time != 0
    GROUP BY 
        role_id
) AS latest ON r.id = latest.role_id
INNER JOIN 
    $this->table l ON l.role_id = latest.role_id 
               AND l.time = latest.latest_time
               AND l.type = $this->LOGINOUT
WHERE 
    r.is_online = 0
    AND r.pf = '$pf'
    AND l.animal_home_time <= UNIX_TIMESTAMP();";

        // Log::write("debug sql2===<>".$sql2 );

        $online_list = Db::query($sql1);
        $animal_list = Db::query($sql2);
        $response = [];
        foreach ($online_list as $row) {
            $response[1][] = $row["account"];
        }

        foreach ($animal_list as $row) {
            $response[2][] = $row["account"];
        }
        return $response;

    }
}